﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Module;
using System.Data;
using Qmaker.DAL;
using EMPower.Utility;

namespace DLL
{
    public class DUserPaperTag
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into UserPaperTag(UserPaperTagId,UserPaperId,UserTagId,DateCreated) values (@UserPaperTagId,@UserPaperId,@UserTagId,@DateCreated)";

        private const string SQL_UPDATE = "update UserPaperTag set UserPaperId=@UserPaperId,UserTagId=@UserTagId,DateCreated=@DateCreated  where UserPaperTagId=@UserPaperTagId";

        private const string SQL_DELETE_PAPERID_TAGID = "delete from UserPaperTag where UserPaperId=@UserPaperId and UserTagId=@UserTagId";

        private const string SQL_DELETE_PAPERID = "delete from UserPaperTag where UserPaperId=@UserPaperId";

        private const string SQL_DELETE_ID = "delete from UserPaperTag where UserPaperTagId=@UserPaperTagId";

        #endregion

        #region 公共接口
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="userPaperTagInfo"></param>
        /// <returns></returns>
        public bool Insert(UserPaperTagInfo userPaperTagInfo)
        {
            if (userPaperTagInfo == null)
                throw new ArgumentNullException("userPaperTagInfo");

            SqlParameter[] parms = GetInsertParms(userPaperTagInfo);
            //parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="paperInfo"></param>
        /// <returns></returns>
        public bool Update(UserPaperTagInfo userPaperTagInfo)
        {
            if (userPaperTagInfo == null || userPaperTagInfo.UserPaperTagId.ToString().Length != 18)
                throw new ArgumentNullException("userPaperTagInfo");

            SqlParameter[] parms = GetInsertParms(userPaperTagInfo);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 根据PagerId和UserTagId删除
        /// </summary>
        /// <param name="userPaperId"></param>
        /// <param name="userTagId"></param>
        /// <returns></returns>
        public int Delete(long userPaperId, long userTagId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@UserPaperId",SqlDbType.BigInt),
                    new SqlParameter("@UserTagId",SqlDbType.BigInt)
                };
            parms[0].Value = userPaperId;
            parms[1].Value = userTagId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_PAPERID_TAGID, parms);

        }

        /// <summary>
        /// 根据PagerId删除
        /// </summary>
        /// <param name="userPaperId"></param>
        /// <returns></returns>
        public int Delete(long userPaperId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@UserPaperId",SqlDbType.BigInt)
                };
            parms[0].Value = userPaperId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_PAPERID, parms);

        }

        /// <summary>
        /// 根据主键删除
        /// </summary>
        /// <param name="userPaperTagId"></param>
        /// <returns></returns>
        public int DeleteById(long userPaperTagId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@UserPaperTagId",SqlDbType.BigInt)
                };
            parms[0].Value = userPaperTagId;


            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_ID, parms);

        }


        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="paperTagInfos"></param>
        /// <returns></returns>
        public bool Insert(List<UserPaperTagInfo> paperTagInfos)
        {
            if (paperTagInfos == null || paperTagInfos.Count == 0)
                return false;
            //DataTable dt = new DataTable();
            ////UserPaperTagId,UserPaperId,UserTagId,DateCreated
            //dt.Columns.Add("UserPaperTagId", typeof(long));
            //dt.Columns.Add("UserPaperId", typeof(long));
            //dt.Columns.Add("UserTagId", typeof(long));
            //dt.Columns.Add("DateCreated", typeof(DateTime));


            //foreach (UserPaperTagInfo l in paperTagInfos)
            //{
            //    DataRow dr = dt.NewRow();
            //    dr["UserPaperTagId"] = l.UserPaperTagId;

            //    dr["UserPaperId"] = l.UserPaperId;
            //    dr["UserTagId"] = l.UserTagId;
            //    dr["DateCreated"] = DateTime.Now;
            //    dt.Rows.Add(dr);
            //}

            DataTable dt = Common.ListToDataTable(paperTagInfos);
            using (SqlConnection conn = new SqlConnection(Qmaker.DAL.SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.BatchSize = 2000;
                        bulkCopy.BulkCopyTimeout = 30;
                        bulkCopy.DestinationTableName = "UserPaperTag";
                        try
                        {
                            foreach (DataColumn col in dt.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            bulkCopy.WriteToServer(dt);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 事物中批量插入
        /// </summary>
        /// <param name="paperTagInfos"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public bool Insert(List<UserPaperTagInfo> paperTagInfos, SqlTransaction trans)
        {
            if (paperTagInfos == null || paperTagInfos.Count == 0)
                return false;

            DataTable dt = Common.ListToDataTable(paperTagInfos);

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(trans.Connection, SqlBulkCopyOptions.Default, trans))
            {
                bulkCopy.BatchSize = 2000;
                bulkCopy.BulkCopyTimeout = 30;
                bulkCopy.DestinationTableName = "UserPaperTag";
                try
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    bulkCopy.WriteToServer(dt);

                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
            return true;
        }

        #endregion


        #region 私有接口
        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(UserPaperTagInfo model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserPaperTagId", SqlDbType.BigInt,8),
					new SqlParameter("@UserPaperId", SqlDbType.BigInt,8),
					new SqlParameter("@UserTagId", SqlDbType.BigInt,8),
					new SqlParameter("@DateCreated", SqlDbType.DateTime)};
            parameters[0].Value = model.UserPaperTagId;
            parameters[1].Value = model.UserPaperId;
            parameters[2].Value = model.UserTagId;
            parameters[3].Value = model.DateCreated;

            return SetInsertParmsCheck(parameters);
        }

        #endregion
    }
}
